using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;


namespace StudentManagementApplication.DAO
{
    public class PersonDAO : BaseDAO
    {
        public PersonDAO()
        {
        }

        public Entities.Person CreatePersonFromReader(IDataReader reader)
        {
            Entities.Person person = new Entities.Person();
            person.PersonID = (string)reader["cPersonID"];
            person.PersonName = (string)reader["vPersonName"];
            person.PersonBirth = (DateTime)reader["dPersonBirth"];
            person.PersonAdd = (string)reader["vPersonAdd"];
            person.PersonPhone = (string)reader["vPersonPhone"];
            person.PersonMail = (string)reader["vPersonMail"];
            person.Picture = (string)reader["vPicture"];
            person.LoginID = (string)reader["cLoginID"];
            return person;
        }

        public void InsertPerson(Entities.Person person)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcInsertPerson", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@vPersonName", person.PersonName));
                command.Parameters.Add(new SqlParameter("@dPersonBirth", person.PersonBirth));
                command.Parameters.Add(new SqlParameter("@vPersonAdd", person.PersonAdd));
                command.Parameters.Add(new SqlParameter("@vPersonPhone", person.PersonPhone));
                command.Parameters.Add(new SqlParameter("@vPersonMail", person.PersonMail));
                command.Parameters.Add(new SqlParameter("@vPicture", person.Picture));
                command.Parameters.Add(new SqlParameter("@cLoginID", person.LoginID));
                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)

            }
        }

        public void UpdatePerson(Entities.Person person)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcUpdatePerson", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cPersonID", person.PersonID));
                command.Parameters.Add(new SqlParameter("@vPersonName", person.PersonName));
                command.Parameters.Add(new SqlParameter("@dPersonBirth", person.PersonBirth));
                command.Parameters.Add(new SqlParameter("@vPersonAdd", person.PersonAdd));
                command.Parameters.Add(new SqlParameter("@vPersonPhone", person.PersonPhone));
                command.Parameters.Add(new SqlParameter("@vPersonMail", person.PersonMail));
                command.Parameters.Add(new SqlParameter("@vPicture", person.Picture));
                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw;
            }
        }

        public void DeletePerson(string sPersonID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcDeletePerson", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cPersonID", sPersonID));
                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw;
            }
        }

        public Entities.Person GetPersonByPersonID(string sPersonID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetPersonByPersonID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("cPersonID", sPersonID));

                connection.Open();
                Entities.Person person = null;
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.Read())
                    {
                        person = CreatePersonFromReader(reader);
                    }
                    //else
                    //    throw;
                }
                return person;
            }
        }

        public Entities.Person GetPersonByLoginID(string sLoginID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetPersonByLoginID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("cLoginID", sLoginID));

                connection.Open();
                Entities.Person person = null;
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.Read())
                    {
                        person = CreatePersonFromReader(reader);
                    }
                    //else
                    //    throw;
                }
                return person;
            }
        }

        public Entities.Person GetNewPersonID()
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetNewPersonID", connection);
                command.CommandType = CommandType.StoredProcedure;

                connection.Open();
                Entities.Person person = null;
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.Read())
                    {
                        person = CreatePersonFromReader(reader);
                    }
                    //else
                    //    throw;
                }
                return person;
            }
        }

        public List<Entities.Person> GetPersonWithClassID(string sClassID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetPersonWithClassID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("cClassID", sClassID));

                connection.Open();
                List<Entities.Person> personList = new List<Entities.Person>();
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while(reader.Read())
                    {
                        personList.Add(CreatePersonFromReader(reader));
                    }
                    //else
                    //    throw;
                }
                return personList;
            }
        }

        public List<Entities.Person> GetTrainerByClassID(string sClassID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetTrainerByClassID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("cClassID", sClassID));

                connection.Open();
                List<Entities.Person> personList = new List<Entities.Person>();
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        personList.Add(CreatePersonFromReader(reader));
                    }
                    //else
                    //    throw;
                }
                return personList;
            }
        }

        public List<Entities.Person> GetPersonWithRoleID(string sRoleID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetPersonWithRoleID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("cRoleID", sRoleID));
                connection.Open();
                List<Entities.Person> personList = new List<Entities.Person>();
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        personList.Add(CreatePersonFromReader(reader));
                    }
                    //else
                    //    throw;
                }
                return personList;
            }
        }

        public List<Entities.Person> GetPersonExceptRoleID(string sRoleID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetPersonExceptRoleID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("cRoleID", sRoleID));
                connection.Open();
                List<Entities.Person> personList = new List<Entities.Person>();
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        personList.Add(CreatePersonFromReader(reader));
                    }
                    //else
                    //    throw;
                }
                return personList;
            }
        }


        public List<Entities.Person> GetAllPersons()
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetAllPersons", connection);
                command.CommandType = CommandType.StoredProcedure;

                connection.Open();
                List<Entities.Person> personList = new List<Entities.Person>();
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        personList.Add(CreatePersonFromReader(reader));
                    }
                    //else
                    //    throw;
                }
                return personList;
            }
        }
    }
}
